<?php
/**
 * Created by PhpStorm.
 * User: admin
 * Date: 2019/5/24
 * Time: 19:37
 */

class DB_MYSQL
{
    private $config = array(
        //连接数据库服务器的地址
        "db_host"		=> "localhost"
        //连接数据库服务器的用户
        ,"db_user"		=> "root"
        //连接数据库服务器的密码
        ,"db_pass"		=> "123456"
        //连接数据库服务器的类型
        ,"db_type"		=> "mysql"
        //连接数据库服务器的端口
        ,"db_port"		=> 3306
        //数据库编码默认采用utf8
        ,"db_charset"	=> "utf8"
        //连接数据库的名称
        ,"db_name"		=> ""
        //数据库连接类型（是否是长连接）
        ,"db_link_type"	=> false
        //表前缀
        ,"table_prefix" => ""
    );
    //数据库连接对象
    private $conn;
    private $db;
    private $tableName = "";
    private $fields = "*";
    private $joins = "";
    private $wheres = "";
    private $groupBys = "";
    private $orders = "";
    private $limits = "";
    private $type = array(
        "insert"    => "",
        "update"    => "",
    );
    private $isGetSql = false;

    public function __construct($C=array(),$fn='')
    {
        error_reporting(5);//1+4
        date_default_timezone_set("Asia/Shanghai");
        if(is_array($C)){
            foreach ($C as $key => $value) {
                $this->config[$key] = $value;
            }
        }
        try{
            $this->conn = mysql_connect("{$this->config['db_host']}:{$this->config['db_port']}",$this->config['db_user'],$this->config['db_pass']);
            $this->db = mysql_select_db($this->config['db_name']);
            if($this->isError()){
                isset($fn) and is_callable($fn) and $fn();
            }else{
                mysql_query("set names {$this->config['db_charset']}");
            }
        }catch (Exception $exception){
            isset($fn) and is_callable($fn) and $fn($exception);
            exit("数据库连接出错！");
        }

    }

    public function where1($field,$options,$condition){
        $args = func_get_args();
        $args_len = count($args);
        if(is_array($field)){
            if(strtolower($options) == 'or'){
                foreach ($field as $key => $value){
                    $this->wheres .= "$key = $value OR";
                }
                $this->wheres = substr($this->wheres,0,strlen($this->wheres)-2);
            }else{
                foreach ($field as $key => $value){
                    $this->wheres .= "$key = '$value' AND";
                }
                $this->wheres .= substr($this->wheres,0,strlen($this->wheres)-3);
            }
        }elseif (is_array($options)){
            //单字段多条件查询 $options 为一维数组 [$opt => $value]
            if(strtolower($args[$args_len-1]) == 'or'){
                foreach ($options as $opt => $value){
                    $this->wheres .= "$field $opt '$value' OR";
                }
                $this->wheres .= substr($this->wheres,0,strlen($this->wheres)-2);
                $this->wheres .= "({$this->wheres})";
            }else{
                foreach ($options as $opt => $value){
                    $this->wheres .= "$field $opt '$value' AND";
                }
                $this->wheres .= substr($this->wheres,0,strlen($this->wheres)-3);
                $this->wheres .= "({$this->wheres})";
            }
        }elseif (in_array(strtolower($options),array("in","not in") )){
            if(is_array($condition)){
                $in_str = "";
                foreach ($condition as $key => $value){
                    $in_str.= "'$value',";
                }
                $in_str = "(".substr($in_str,0,strlen($in_str)-1).")";
                $this->wheres .= "$field $options $in_str";
            }else{
                $this->wheres .= "$field $options ($condition)";
            }
        }else{
            $this->wheres .= "$field $options '$condition' ";
        }
        $this->wheres = $this->wheres." AND ";
        return $this;
    }

    public function where($field = '', $options = '', $condition = '')
    {
        $args = func_get_args();
        $args_len = count($args);
        $where_str = '';
        if(is_array($field) && !is_array($options) && !is_array($condition)){
            $or_and = strtoupper($options);
            foreach ($field as $key => $value){
                $where_str .= "$key = '$value' $or_and ";
            }
            $this->wheres .= rtrim($where_str,"$or_and ");
        }elseif($field !='' && !is_array($field) && is_array($options) && in_array(strtolower($args[$args_len-1]), array('or','and'))){
            $field_arr = explode('|',$field);
            $or_and = strtoupper(trim($args[$args_len-1]));
            if(is_array($field_arr) && count($field_arr)>1){
                //多字段相同条件查询 $options 为一维数组 array("$opt","$value")【$opt为where里面的运算字符如： =|<>|<|>|in|not in|like|not like|regexp|...】
                foreach ($field_arr as $field) {
                    $opt = $options[0]; $val = $options[1];
                    $where_str .= "$field $opt '$val' $or_and ";
                }
                $where_str = rtrim($where_str,"$or_and ");
            }else{
                //单字段多条件查询 $options 为二维数组 array(array("=","$value"),array("$opt1","$value1"),..)
                foreach ($options as $key => $value) {
                    if(is_array($value)){
                        $opt = $value[0]; $val = $value[1];
                        $where_str .= "$field $opt '$val' $or_and ";
                    }
                }
                $where_str = substr($where_str,0,strlen($where_str)-strlen($or_and)-1);
            }
            $this->wheres .= "($where_str)";
        }elseif (is_string($field) && $field !='' && in_array(strtolower($options), array('in','not in'))){
            if(is_array($condition)){
                foreach ($condition as $key => $value){
                    $where_str .= "'$value',";
                }
                $where_str = "(".substr($where_str,0,strlen($where_str)-1).")";
                $this->wheres .= "$field $options $where_str";
            }else{
                $this->wheres .= "$field $options ($condition)";
            }
        }elseif (is_string($field) && $field !='' && empty($condition)){
            //where 的字符串条件查询，也可以进行参数绑定(如： $this->where("field = :name", array("name"=>"value") ))
            if(is_array($options) && count($options)>0){
                $where_str = $field;
                foreach ($options as $key => $value){
                    $where_str = str_replace(":$key", "'$value'", $where_str);
                }
            }elseif($options !='' && !is_array($options) && !is_object($options)){
                $where_str = "$field = '$options'";
            }else{
                $where_str = $field;
            }
            $this->wheres .= "($where_str)";
        }else{
            $this->wheres .= "$field $options '$condition' ";
        }
        $this->wheres = $this->wheres." AND ";
        return $this;
    }

    public function whereOr($field=null,$options=null,$condition=null){
        $this->where($field,$options,$condition);
        $where_str = $this->wheres;
        $this->wheres = substr($where_str,0,strlen($where_str)-4)."  OR ";
        return $this;
    }

    /*
     * 左连接
     * @param $join string 如： tableName t
     * @param $condition str 如： t.id = a.id
     * */
    public function leftJoin($join='',$condition=''){
        $this->joins .= "LEFT JOIN $join ON $condition ";
        return $this;
    }
    /*
     * 右连接
     * @param $join string 如： tableName t
     * @param $condition str 如： t.id = a.id
     * */
    public function rightJoin($join='',$condition=''){
        $this->joins .= "RIGHT JOIN $join ON $condition ";
        return $this;
    }
    public function join($join='',$condition=''){
        $this->joins .= "JOIN $join ON $condition ";
        return $this;
    }
    /**
     * 截取数据范围
     * @param $offset number 开始位置（从0开始）
     * @param $length number 截取的长度
     */
    public function limit($offset='',$length=''){
        if($offset !== '' || intval($offset)>=0){
            if(!empty($length)){
                $this->limits = "LIMIT $offset ,$length";
            }else{
                $this->limits = "LIMIT $offset";
            }
        }
        return $this;
    }
    /**
     * 排序
     * @param $field string|array 字段名，数组时必须是一维关联数组
     * @param $order string 排序类型 (desc|ASC)
     */
    public function order($field,$order){
        if(is_array($field)){
            foreach ($field as $key => $value){
                $this->orders .= "$key $value ,";
            }
        }else{
            $this->orders .= "$field $order ,";
        }
        //$this->orders = substr($this->orders,0,strlen($this->orders)-1 );
        return $this;
    }

    /**
     * 分组
     * @param string $field
     * @param bool $with_rollup  可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
     * @return $this
     */
    public function groupBy($field = '',$with_rollup = false){
        $this->groupBys = rtrim(" $field");
        ($with_rollup) and ($this->groupBys = " $field WITH ROLLUP");
        return $this;
    }

    /**
     * 表名
     * @param $tableName string 表名（tableName、db.tableName）
     * @param $alias string 表的别名 (tableName t|tableName as t)
     */
    public function table($tableName='',$alias=''){
        self::init();
        if(!empty($alias)){
            $this->tableName = " $tableName $alias ";
        }else{
            $this->tableName = " $tableName ";
        }
        return $this;
    }

    public function name($name){
        return self::table($this->config['table_prefix'].$name);
    }

    /**
     * 查询的字段
     * @param $field string 字段 （field1,field2,t.*,c.f1,c.f2..）
     */
    public function field($field){
        $this->fields = !empty($field) ? $field : "*";
        return $this;
    }

    public function data($field,$value=null){
        if(is_array($field)){
            $k=""; $v=""; $fields="";
            foreach ($field as $key => $value) {
                $k.="$key,";
                $v.="'$value',";
                $fields.= "$key='$value',";
            }

            $k=substr($k,0,strlen($k)-1);
            $v=substr($v,0,strlen($v)-1);
            $fields=substr($fields,0,strlen($fields)-1);

            $this->type['insert'] = " ( $k )values( $v ) ";
            $this->type['update'] = " $fields ";
        }else{
            if(!empty($field) && !empty($value)){
                $this->type['insert'] = "($field)VALUES($value)";
                $this->type['update'] = "$field='$value'";
            }elseif (!empty($field) && empty($value)){
                $this->type['update'] = $this->type['insert'] = $field;
            }else{
                $this->type['update'] = $this->type['insert'] = "";
            }
        }
        return $this;
    }

    /**
     * 更新语句(更新的数据需要使用SQL函数)
     * @param string $field
     * @param string $value
     * @return $this
     */
    public function exp($field='',$value=''){
        if(is_array($field)){
            foreach ($field as $key => $v){
                $this->exp($key,$v);
            }
        }elseif($field !== '' && $value !== ''){
            if(strlen($this->type['update'])<1 || substr($this->type['update'],-1) == ','){
                $this->type['update'] .= " $field=$value,";
            }else{
                $this->type['update'] .= ",$field=$value";
            }
        }
        return $this;
    }

    /**
     * 返回查询结果 （多条数据，二维数组）
     * @return array|boolean
     */
    public function select(){
        $sql = $this->sql_select_str_exec();
        if($this->isGetSql == true){ return $sql; }
        $re = mysql_query($sql,$this->conn);
        if($re){
            $arr = array();
            while ($row = mysql_fetch_array($re,MYSQL_ASSOC)){
                $arr[] = $row;
            }
            mysql_free_result($re);
            return $arr;
        }
        return false;
    }

    public function find(){
        $sql = $this->sql_select_str_exec();
        if($this->isGetSql == true){ return $sql; }
        $re = mysql_query($sql,$this->conn);
        if($this->isError()){
            return false;
        }
        return mysql_fetch_array($re,MYSQL_ASSOC);
    }

    public function count($field='*'){
        $this->sql_str_exec();
        $this->fields = $this->fields && $this->fields != "*" ? rtrim(trim($this->fields),",")."," : "";
        $num = "num_".time();
        $sql = "SELECT {$this->fields}COUNT($field) AS $num FROM {$this->tableName} {$this->joins} {$this->wheres} {$this->orders} {$this->limits}";
        if($this->isGetSql == true){ return $sql; }
        $re = mysql_query($sql,$this->conn);
        if($this->isError()){
            return false;
        }
        $row = mysql_fetch_array($re,MYSQL_ASSOC);
        return $row[$num];
    }
    /**
     * 获取某个字段的值
     * @param $field string 字段名（单个字段）
     * @return string 返回字段值
     */
    public function value($field=''){
        $sql = $this->sql_select_str_exec();
        if($this->isGetSql == true){ return $sql; }
        $re = mysql_query($sql,$this->conn);
        if($this->isError()){
            return false;
        }
        $row = mysql_fetch_array($re,MYSQL_ASSOC);
        return $row[$field];
    }


    public function insert($data=''){
        if(!empty($data)){
            $this->data($data);
        }
        $sql = "INSERT INTO {$this->tableName}{$this->type['insert']}";
        if($this->isGetSql == true){ return $sql; }
        $re = mysql_query($sql,$this->conn);
        if($this->isError()){
            return false;
        }
        return mysql_affected_rows($this->conn); //影响的行数
    }

    public function insertGetId($data=''){
        if(!empty($data)){
            $this->data($data);
        }
        $sql = "INSERT INTO {$this->tableName}{$this->type['insert']}";
        if($this->isGetSql == true){ return $sql; }
        $re = mysql_query($sql,$this->conn);
        if($this->isError()){
            return false;
        }
        $insertId = mysql_insert_id($this->conn); //获取插入的自增id 字段的值
        return $insertId;
    }

    public function update($data='')
    {
        if (!empty($data)) {
            $this->data($data);
        }
        $update_length = strlen($this->type['update']);
        //如果字符","在最后一位，去掉最后一个字符","
        (substr($this->type['update'],-1) == ',') and ($this->type['update']=substr($this->type['update'],0,$update_length-1));
        //如果字符","在首位,去掉首位的字符","
        (strpos($this->type['update'],',') < 1) and ($this->type['update'] = substr($this->type['update'],1,$update_length-1));
        $this->orders = empty($this->orders) ? "" : "ORDER BY " . substr($this->orders, 0, strlen($this->orders) - 1);
        $this->wheres = empty($this->wheres) ? "" : "WHERE " . substr($this->wheres, 0, strlen($this->wheres) - 4);
        $sql = "UPDATE {$this->tableName} SET {$this->type['update']} {$this->wheres} {$this->orders} {$this->limits}";
        if($this->isGetSql == true){ return $sql; }
        $re = mysql_query($sql,$this->conn);
        if ($this->isError()) {
            return false;
        }
        return mysql_affected_rows($this->conn); //影响的行数
    }

    public function delete(){
        self::sql_str_exec();
        $sql = "DELETE FROM {$this->tableName} {$this->wheres} {$this->orders} {$this->limits}";
        if($this->isGetSql == true){ return $sql; }
        $re = mysql_query($sql,$this->conn);
        if ($this->isError()) {
            return false;
        }
        return mysql_affected_rows($this->conn); //影响的行数
    }


    public function sql_select($sql){
        if(strpos($sql,'SELECT') !== 0 || strpos($sql,'select') !== 0){ return false; }
        $re = mysql_query($sql,$this->conn);
        if(self::isError()){
            return false;
        }
        if($re){
            $arr = array();
            while ($row = mysql_fetch_array($re,MYSQL_ASSOC)){
                $arr[] = $row;
            }
            mysql_free_result($re);
            return $arr;
        }
    }
    public function getAll($sql){
        return self::sql_select($sql);
    }
    /**
     * 返回一维数组（一条数据）
     * @param $sql string sql查询语句
     * @param $error_fn callable 错误回调函数
     */
    public function getRow($sql){
        if(strpos($sql,'SELECT') !== 0 || strpos($sql,'select') !== 0){ return false; }
        $re = mysql_query($sql,$this->conn);
        if($re){
            return mysql_fetch_array($re,MYSQL_ASSOC);
        }
    }
    public function getOne($sql,$field=''){
        if(strpos($sql,'SELECT') !== 0 || strpos($sql,'select') !== 0){ return false; }
        $re = mysql_query($sql,$this->conn);
        if($re){
            $row = mysql_fetch_array($re,MYSQL_ASSOC);
            return $row[$field];
        }
        return false;
    }
    /**
     * 插入数据
     * @param $sql string|array 插入语句或者一维关联数组
     * @param $tableName string 表名（当$sql为数组时有效）
     * @param $get_insertId number (1=返回插入的id)
     * @return boolean|number
     */
    public function sql_insert($sql,$tableName='',$get_insertId=1,$mode=0){
        if(is_array($sql) && !empty($tableName)){
            $k=""; $v=""; $field="";
            foreach ($sql as $key => $value) {
                $k.="$key,";
                $v.="'$value',";
                $field.="$key='$value',";
            }
            $k=substr($k,0,strlen($k)-1);
            $v=substr($v,0,strlen($v)-1);
            $field=substr($field,0,strlen($field)-1);
            $sql_str="INSERT INTO $tableName( $k )values( $v )";
            $sql_strs="INSERT INTO $tableName SET $field ";
            $re = mysql_query($mode ? $sql_str : $sql_strs, $this->conn);
        }else{
            $re = mysql_query($sql,$this->conn);
        }
        if(self::isError()){
            return false;
        }
        if($re){
            $insertId = mysql_insert_id($this->conn);
            if($get_insertId == 1){
                //插入的自增id字段的值
                return $insertId;
            }
        }
        return mysql_affected_rows($this->conn); //影响的行数
    }

    public function sql_update($sql,$tableName='',$wheres=''){
        if(is_array($sql) && !empty($tableName)){
            $field="";
            foreach ($sql as $key => $value) {
                $field.="$key='$value',";
            }
            $field=substr($field,0,strlen($field)-1);
            $sql_str="UPDATE $tableName SET $field $wheres ";
            $re = mysql_query($sql_str,$this->conn);
        }else{
            $re = mysql_query($sql,$this->conn);
        }
        if(self::isError()){
            return false;
        }
        return mysql_affected_rows($this->conn); //影响的行数
    }

    public function sql_delete($sql){
        $re = mysql_query($sql,$this->conn);
        if(self::isError()){
            return false;
        }
        return mysql_affected_rows($this->conn); //影响的行数
    }


    /**
     * 初始化变量（sql语句拼接）
     */
    public function init(){
        $this->fields = "*";
        $this->joins = "";
        $this->wheres = "";
        $this->groupBys = "";
        $this->orders = "";
        $this->limits = "";
        $this->type['insert'] = "";
        $this->type['update'] = "";
        return $this;
    }
    /**
     * 获取sql语句
     * @param $type boolean (true=获取sql语句，默认false)
     * @return object
     */
    public function getSql($type = true){
        $this->isGetSql = $type;
        return $this;
    }

    /**
     * 创建视图
     * @param string $tableName 视图名称【前缀 v_】
     * @param string $sql 查询语句字符串
     * @return $this
     */
    public function create_view($tableName = '', $sql = ''){
        $pre = "v_";
        $tableName && ($tableName = $pre.$tableName);
        //先判断删除视图
        $re = mysql_query("DROP VIEW IF EXISTS `$tableName` ;", $this->conn);
        //创建视图
        $re = mysql_query("CREATE VIEW `$tableName` AS $sql ;", $this->conn);
        return $this;
    }

    /**
     * 创建临时表
     * @param string $tableName 表名【自动添加表前缀 temporary_】
     * @param string $sql 查询语句字符串
     * @return $this
     */
    public function create_temp_table($tableName = '',$sql=''){
        $pre = "temporary_";
        $tableName && ($tableName = $pre.$tableName);
        //先判断删除临时表
        $re = mysql_query("DROP TABLE IF EXISTS `$tableName` ;", $this->conn);
        //创建临时表
        $re = mysql_query("CREATE TEMPORARY TABLE `$tableName` $sql ;", $this->conn);
        return $this;
    }

    /**
     * 处理sql语句的拼接
     * @return object
     */
    protected function sql_str_exec(){
        $this->orders = empty($this->orders) ? "" : "ORDER BY ".substr($this->orders,0,strlen($this->orders)-1);
        $this->wheres = empty($this->wheres) ? "" : "WHERE ".substr($this->wheres,0,strlen($this->wheres)-4);
        $this->groupBys = empty($this->groupBys) ? "" : "GROUP BY " . $this->groupBys;
        return $this;
    }
    /**
     * sql查询语句
     * @return string 返回查询语句字符串
     */
    protected function sql_select_str_exec(){
        self::sql_str_exec();
        return $sql = "SELECT {$this->fields} FROM {$this->tableName} {$this->joins} {$this->wheres} {$this->groupBys} {$this->orders} {$this->limits}";
    }
    /**
     * sql语句操作数据库是否出错
     * @return boolean (true=出错，false=未出错)
     */
    public function isError(){
        //return $this->conn->errno != 0;
        //return mysqli_sqlstate($this->conn) !== "00000";

        //return mysql_errno($this->conn) !== 0;
        return mysql_error($this->conn) !== "";

    }
    /**
     * 获取错误信息
     * @param $type integer (0=获取错误标识,1=数据库错误码,2=错误描述)
     * @return string|int|array
     */
    public function getError($type=0){
        $err = array();
        //$err[] = mysql_sqlstate(); //数据库sql错误标识
        $err[] = mysql_errno($this->conn);  //错误码
        $err[] = mysql_error($this->conn);  //sql错误描述
        return in_array($type, array(0,1,2) ) ? $err[$type] : $err;
        //$this->conn->error_list;
    }


    public function get_fields(){
        return trim($this->fields);
    }
    public function get_joins(){
        return trim($this->joins);
    }
    /**
     * 设置 join 部分条件字符串
     * @param string|array $joins  字符串或者一维关联数组（键值是表名称如 xxx t，值是连接条件如 t.xx = t1.xx）
     * @param array $join  表连接方式【传入一维关联数组,键值是表名如 xxx t, 值是连接方式如 LEFT JOIN】
     * @return $this
     */
    public function set_joins($joins = '', $join = array('tableName' => 'LEFT JOIN')){
        $joins || ($joins = $this->get_joins());
        $joins_str = '';
        if (is_array($joins) && count($joins) > 0 && is_array($join) && count($join) > 0) {
            foreach ($joins as $tableName => $condition) {
                $join[$tableName] or ($join[$tableName] = "JOIN");
                $joins_str .= "{$join[$tableName]} $tableName ON $condition ";
            }
            $joins = $joins_str;
        }
        $this->joins = $joins;
        return $this;
    }

    /**
     * 获取 where 部分字符串
     * @return string
     */
    public function get_where(){
        //self::sql_str_exec();
        $wheres = empty($this->wheres) ? "" : substr($this->wheres,0,strlen($this->wheres)-4);
        return ltrim($wheres,'WHERE');
    }
    /**
     * 设置或处理 where 部分字符串
     * @param string $wheres
     * @param array $param
     * @param int $is_init 是否初始化【覆盖】
     * @return $this
     */
    public function set_wheres($wheres = '', $param = array(), $is_init = 1){
        $wheres = $wheres && is_string($wheres) ? $wheres : trim($this->wheres, "WHERE");
        if (is_array($param) && count($param) > 0) {
            foreach ($param as $key => $value) {
                $wheres = str_replace(":$key", "'$value'", $wheres);
            }
        }
        $is_init && ($this->wheres = preg_match("/(OR|AND)$/i", rtrim($wheres)) ? "WHERE $wheres    " : "WHERE $wheres");
        return $this;
    }
    public function get_groupBys(){
        return trim($this->groupBys);
    }
    public function get_orders(){
        return trim($this->orders);
    }
    public function get_limits(){
        return trim($this->limits);
    }

    /**
     * 浏览器友好的变量输出
     * @param mixed $var 变量
     * @param boolean $echo 是否输出 默认为True 如果为false 则返回输出字符串
     * @param string $label 标签 默认为空
     * @param boolean $strict 是否严谨 默认为true
     * @return void|string
     */
    public function dump($var, $echo=true, $label=null, $strict=true) {
        $label = ($label === null) ? '' : rtrim($label) . ' ';
        if (!$strict) {
            if (ini_get('html_errors')) {
                $output = print_r($var, true);
                $output = '<pre>' . $label . htmlspecialchars($output, ENT_QUOTES) . '</pre>';
            } else {
                $output = $label . print_r($var, true);
            }
        } else {
            ob_start();
            var_dump($var);
            $output = ob_get_clean();
            if (!extension_loaded('xdebug')) {
                $output = preg_replace('/\]\=\>\n(\s+)/m', '] => ', $output);
                $output = '<pre>' . $label . htmlspecialchars($output, ENT_QUOTES) . '</pre>';
            }
        }
        if ($echo) {
            echo($output);
            return null;
        }else
            return $output;
    }

}
